package org.lys.jdbc;

import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

/**
 * @Description:jdbc连接工具类
 * @Copyright: 福州骏华信息有限公司 (c)2013
 * @Created Date : 2013-4-2
 * @Author lys
 */
public final class JdbcUtils {
	private static String url = "jdbc:mysql://localhost:3306/javaTest";
	private static String user = "root";
	private static String password = "root";
	
	private static DataSource myDataSource = null;

	private JdbcUtils() {
	}

	static {
		try {
//			Class.forName("com.mysql.jdbc.Driver");
			Properties pro = new Properties();
			pro.load(JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties"));
			myDataSource = BasicDataSourceFactory.createDataSource(pro);
		} catch (Exception e) {
			throw new ExceptionInInitializerError(e);
		}
	}
	/**
	 * @Description: 取得数据库连接
	 * @Created Time: 2013-4-2 下午9:00:05
	 * @Author lys
	 * @return
	 * @throws SQLException
	 */
	public static Connection getConnection() throws SQLException {
//		return DriverManager.getConnection(url, user, password);
		return myDataSource.getConnection();
	}
	/**
	 * @Description: 释放资源
	 * @Created Time: 2013-4-2 下午9:00:26
	 * @Author lys
	 * @param rs
	 * @param st
	 * @param conn
	 */
	public static void free(ResultSet rs, Statement st, Connection conn) {
		try {
			if (rs != null){
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (st != null){
					st.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				if (conn != null){
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
//					myDataSource.free(conn);
				}
			}
		}
	}
	/**
	 * @Description: 查询返回Map
	 * @Created Time: 2013-4-4 上午8:50:43
	 * @Author lys
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public static List<Map<String, Object>> queryMap(String sql, Object[] params) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			ps = conn.prepareStatement(sql);
			for (int i = 1; i <= params.length; i++) {
				ps.setObject(i, params[i - 1]);
			}
			rs = ps.executeQuery();
			
			String[] colNames = getColNames(rs);
			
			List<Map<String, Object>> datas = new ArrayList<Map<String, Object>>();

			while (rs.next()) {
				Map<String, Object> data = new HashMap<String, Object>();
				for (int i = 0; i < colNames.length; i++) {
					data.put(colNames[i], rs.getObject(colNames[i]));
				}
				datas.add(data);
			}
			return datas;
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	/**
	 * @Description: 取得返回结果集的列名称
	 * @Created Time: 2013-4-4 上午9:36:48
	 * @Author lys
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	public static String[] getColNames(ResultSet rs) throws SQLException {
		ResultSetMetaData rsmd = rs.getMetaData();
		int count = rsmd.getColumnCount();
		String[] colNames = new String[count];
		for (int i = 1; i <= count; i++) {
			colNames[i - 1] = rsmd.getColumnLabel(i);
		}
		return colNames;
	}
	/**
	 * @Description: 查询返回对应的实体类
	 * @Created Time: 2013-4-4 上午9:37:13
	 * @Author lys
	 * @param sql
	 * @param params
	 * @param clazz
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("rawtypes")
	public static List<Object> query(String sql, Object[] params,Class clazz) throws Exception{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			ps = conn.prepareStatement(sql);
			for (int i = 1; i <= params.length; i++) {
				ps.setObject(i, params[i - 1]);
			}
			rs = ps.executeQuery();
			
			String[] colNames = getColNames(rs);
			
			List<Object> datas = new ArrayList<Object>();

			while (rs.next()) {
				Object data = clazz.newInstance();
				for (int i = 0; i < colNames.length; i++) {
					String colName = colNames[i];
					colName = colName.substring(0,1).toUpperCase()+colName.substring(1, colName.length());
					String methodName = "set"+colName;
					Method[] methods = clazz.getDeclaredMethods();
					for (Method method : methods) {
						if(method.getName().equals(methodName)){
							method.invoke(data, rs.getObject(colName));
						}
					}
					
				}
				datas.add(data);
			}
			return datas;
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	/**
	 * @Description: 取得实体
	 * @Created Time: 2013-4-4 上午9:39:47
	 * @Author lys
	 * @param sql
	 * @param params
	 * @param clazz
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("rawtypes")
	public static Object get(String sql, Object[] params,Class clazz) throws Exception{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Object data = null;
		try {
			conn = JdbcUtils.getConnection();
			ps = conn.prepareStatement(sql);
			for (int i = 1;params!=null&& i <= params.length; i++) {
				ps.setObject(i, params[i - 1]);
			}
			rs = ps.executeQuery();
			
			String[] colNames = getColNames(rs);
			
			if(rs.next()) {
				data =  clazz.newInstance();
				for (int i = 0; i < colNames.length; i++) {
					String colName = colNames[i];
					colName = colName.substring(0,1).toUpperCase()+colName.substring(1, colName.length());
					String methodName = "set"+colName;
					Method[] methods = clazz.getDeclaredMethods();
					for (Method method : methods) {
						if(method.getName().equals(methodName)){
							method.invoke(data, rs.getObject(colName));
						}
					}
				}
			}
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
		return data;
	}
}
